package spring.jdbc.cases;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Before;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnResultSet;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

public class JdbcTemplateTest3 {
    private static JdbcTemplate jdbcTemplate = new JdbcTemplate();

    @BeforeClass
    public static void setUpClass() {
        String url = "jdbc:hsqldb:mem:test";
        String username = "root";
        String password = "root";
        DriverManagerDataSource dataSource = new DriverManagerDataSource(url, username, password);
        dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
        jdbcTemplate = new JdbcTemplate(dataSource);
    }


    @Before("")
    public void setUp() {
        String createTableSql = "create memory table test (" + "id int generated by default as identity primary key,"
                + "name varchar(100))";
        jdbcTemplate.update(createTableSql);
        String createHsqldbFunctionSql = " create function function_test(str char(100))"
                + "returns int begin atomic return length(str);end";
        jdbcTemplate.update(createHsqldbFunctionSql);
        String createHsqldbProcedureSql = "" + "create procedure procedure_test"
                + "(inout inOutName varchar(100),out outid int)" + "modifies sql data " + "" + "begin atomic " + ""
                + "insert into test(name) values (intOutName);" + "set inOutName='Hello,'+inOutName;" + "" + "end";
        jdbcTemplate.execute(createHsqldbProcedureSql);
    }


    @After("")
    public void tearDown() {
        jdbcTemplate.execute("drop function function_test");
        jdbcTemplate.execute("drop procedure procedure_test");
        String dropTableSql = "drop table test";
        jdbcTemplate.execute(dropTableSql);
    }

    @Test
    public void testCallableStatementCreator1() {
        final String callFunctionSql = "{call function_test(?)}";
        List<SqlParameter> params = new ArrayList<SqlParameter>();
        params.add(new SqlParameter(Types.VARCHAR));
        params.add(new SqlReturnResultSet("result", new ResultSetExtractor<Integer>() {

            @Override
            public Integer extractData(ResultSet rs) throws SQLException, DataAccessException {
                while (rs.next()) {
                    return rs.getInt(1);
                }
                return 0;
            }
        }));
        Map<String, Object> outValues = jdbcTemplate.call(new CallableStatementCreator() {

            @Override
            public CallableStatement createCallableStatement(Connection conn) throws SQLException {
                CallableStatement cstmt = conn.prepareCall(callFunctionSql);
                cstmt.setString(1, "test");
                return cstmt;
            }
        }, params);
    }


}
